﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
//引入命名空间
using System.Data.SQLite;
using System.Data.SQLite.Generic;
using System.Data.Common;
using System.Data;
using Dapper;

namespace SQLiteDemo
{
    public class SQLiteHelper
    {
        /// <summary>
        /// SQLite数据库文件存放物理地址
        /// </summary>
        private static readonly string strDataSource = string.Format(@"{0}\data.db", Directory.GetCurrentDirectory());

        /// <summary>
        /// SQLite连接字符串，刚开始没有，暂时留空
        /// </summary>
        private static string strConnectionString = string.Empty;

        /// <summary>
        /// 构造函数
        /// </summary>
        public SQLiteHelper()
        {
            if (string.IsNullOrEmpty(strConnectionString))
            {
                SQLiteConnectionStringBuilder scBuilder = new SQLiteConnectionStringBuilder();
                scBuilder.DataSource = strDataSource;   //SQLite数据库地址
                //scBuilder.Password = "123456";        //密码(使用后，其他管理程序无法查看)
                strConnectionString = scBuilder.ToString();
                using (SQLiteConnection connection = new SQLiteConnection(strConnectionString))
                {
                    //检测文件是否存在
                    if (!File.Exists(strDataSource))
                    {
                        //创建数据库文件
                        SQLiteConnection.CreateFile(strDataSource);
                        //打开数据连接
                        connection.Open();
                        //Command
                        SQLiteCommand command = new SQLiteCommand(connection);
                        command.CommandText = "CREATE TABLE tb_User (ID INTEGER PRIMARY KEY AUTOINCREMENT, Name varchar (60));";
                        command.CommandType = System.Data.CommandType.Text;
                        //执行SQL
                        int iResult = command.ExecuteNonQuery();
                        //可省略步骤=======关闭连接
                        connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string strSQL, SQLiteParameter[] parameters)
        {
            int iResult = 0;
            using (SQLiteConnection connection = new SQLiteConnection(strConnectionString))
            {
                //打开数据连接
                connection.Open();
                //Transaction
                using (DbTransaction transaction = connection.BeginTransaction())
                {
                    //Command
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        command.CommandText = strSQL;
                        //SQLite只支持CommandType.Text
                        command.CommandType = System.Data.CommandType.Text;
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        //执行SQL
                        iResult = command.ExecuteNonQuery();
                    }
                    transaction.Commit(); 
                }
                //可省略步骤=======关闭连接
                connection.Close();
            }
            return iResult;
        }

        /// <summary>
        /// 执行一个查询语句，返回查询结果的第一行第一列
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string strSQL, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(strConnectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(strSQL, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    object obj = command.ExecuteScalar();
                    return obj;
                }
            }
        } 

        /// <summary>
        /// 执行一个查询语句，返回一个包含查询结果的DataTable
        /// </summary>
        /// <param name="strSQL">要执行的查询语句</param>
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string strSQL, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(strConnectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(strSQL,connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }

        }


        public List<T> ExecuteList<T>(string strSQL, object parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(strConnectionString))
            {
                connection.Open();
                return connection.Query<T>(strSQL, parameters).ToList();
            }
        }

    }
}
